package DBLayer;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;

import ModelLayer.Child;
import ModelLayer.Trainer;
import ModelLayer.TrainingDay;
import ModelLayer.TrainingProgram;

public class DBTrainingDay
{
	private Connection con;
    
    public DBTrainingDay()
    {
    	con = DBConnection.getInstance().getDBcon();
    }
	
    public ArrayList<TrainingDay> findAllTrainingDay(boolean retriveAssociation)
    {
        return miscWhere("", retriveAssociation);
    }

    public TrainingDay searchTrainingDayID(int trainingDayID, boolean retriveAssociation)
    {   
    	String wClause = "td.TrainingDayID = " + trainingDayID + "";
        return singleWhere(wClause, retriveAssociation);
    }
    
    public TrainingDay searchTrainingDayDate(String trainingDayDate, boolean retriveAssociation)
    {   
    	String wClause = "td.PMDate = '" + trainingDayDate + "'";
        return singleWhere(wClause, retriveAssociation);
    }
    
    private ArrayList<TrainingDay> miscWhere(String wClause, boolean retrieveAssociation)
	{
		ResultSet results;
		ArrayList<TrainingDay> trainingDayList = new ArrayList<TrainingDay>();	
		
		String query = buildQuery(wClause);
  
  		try
  		{
	 		Statement stmt = con.createStatement();
	 		stmt.setQueryTimeout(200);
	 		results = stmt.executeQuery(query);
	 		
			while( results.next() )
			{
				TrainingDay tdObj = new TrainingDay();
				tdObj = buildTrainingDay(results);	
				trainingDayList.add(tdObj);	
			}
			stmt.close();
		}
	 	catch(Exception e)
	 	{
	 		System.out.println("Query exception - select: "+e);
			e.printStackTrace();
	 	}
		return trainingDayList;
	}
	
	private TrainingDay singleWhere(String wClause, boolean retrieveAssociation)
	{
		ResultSet results;
		TrainingDay tdObj = new TrainingDay();
	    String query = buildQuery(wClause);
//        System.out.println(query);
		try
		{
	 		Statement stmt = con.createStatement();
	 		stmt.setQueryTimeout(200);
	 		results = stmt.executeQuery(query);
	 		
	 		if( results.next() )
	 		{
	 			tdObj = buildTrainingDay(results);
	 			stmt.close();
			}
		}	
	 	catch(Exception e)
	 	{
	 		System.out.println("Query exception: "+e);
	 	}
		return tdObj;
	}

	private String buildQuery(String wClause)
	{
	    String query="SELECT td.TrainingDayID, td.PMDate, td.TrainingStart, td.TrainingEnd, td.Intensity, t.TrainerID, trp.FirstName, trp.LastName, c.ChildID, c.CPR, cp.FirstName AS cFirstName, cp.LastName AS cLastName, tp.TrainingProgramID, tp.Name FROM TrainingDay td, Child c, Trainer t, Grownup g, Person cp, Person trp, TrainingProgram tp";
	    query=query+" WHERE td.ChildID = c.ChildID AND c.PersonID = cp.PersonID AND td.TrainerID = t.TrainerID AND g.GrownupID = t.GrownupID AND g.PersonID = trp.PersonID AND td.TrainingProgramID = tp.TrainingProgramID";
		if (wClause.length()>0)
		{
			query=query+" AND "+ wClause;
		}
		return query;
	}

	private TrainingDay buildTrainingDay(ResultSet results)
      {   
		TrainingDay tdObj = new TrainingDay();
		Child child = new Child();
		Trainer trainer = new Trainer();
		TrainingProgram trainingProgram = new TrainingProgram();
          try
          {
                tdObj.setTrainingDayID(results.getInt("TrainingDayID"));
                tdObj.setDate(results.getString("PMDate"));
                tdObj.setTrainingStart(results.getString("TrainingStart"));
                tdObj.setTrainingEnd(results.getString("TrainingEnd"));
                tdObj.setIntensity(results.getInt("Intensity"));
                trainer.setTrainerID(results.getInt("TrainerID"));
                trainer.setfName(results.getString("FirstName"));
                trainer.setlName(results.getString("LastName"));
                tdObj.setTrainer(trainer);
                child.setChildID(results.getInt("ChildID"));
                child.setCPR(results.getString("CPR"));
                child.setfName(results.getString("cFirstName"));
                child.setlName(results.getString("cLastName"));
                tdObj.setChild(child);
                trainingProgram.setTrainingProgramID(results.getInt("TrainingProgramID"));
                trainingProgram.setName(results.getString("Name"));
                tdObj.setTrainingProgram(trainingProgram);
          }
         catch(Exception e)
         {
             System.out.println("error in building the TrainingDay object");
         }
         return tdObj;
      }
}
